﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAL;
using BLL;
using System.Data.SqlClient;
using Xuatfile;
using System.IO;

namespace _3tiers
{
    public partial class tab1_Form_XemLichDay : Form
    {
        DataTable dt1;
        int k = 0;
        DataAccess ac = new DataAccess();
        BLL.BLL business = new BLL.BLL();
        public tab1_Form_XemLichDay()
        {
            InitializeComponent();
            //string sql3 = "select khoa.TenKhoa as Khoa,gv.TenGiaoVien as Ten_Giao_Vien,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblChiTietLich chitiet,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and chitiet.IDChiTietLich=ld.IDChiTietLich and m.IDMon=chitiet.IDMon and khoa.IDKhoa=gv.IDKhoa and chitiet.IDNhom=nh.IDNhom ";
            //string sql3 = "select khoa.TenKhoa as Khoa,gv.TenGiaoVien as Ten_Giao_Vien,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and ld.IDMon=m.IDMon and khoa.IDKhoa=gv.IDKhoa and ld.IDNhom=nh.IDNhom";
            string sql3 = "stSelectAllLichDayGiaoVien";
            dataGridView1.DataSource = business.ShowData(sql3);
            dt1 = ac.GetTable(sql3);
        }        
        
        //string idKhoa;
        private void tab1_Form_XemLichDay_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'namHocDataSet.tblKi' table. You can move, or remove it, as needed.
            this.tblKiTableAdapter1.Fill(this.namHocDataSet.tblKi);
            // TODO: This line of code loads data into the 'nhomDataSet17.tblNhom' table. You can move, or remove it, as needed.
            this.tblNhomTableAdapter.Fill(this.nhomDataSet17.tblNhom);
            // TODO: This line of code loads data into the 'hocKyDataSet.tblKi' table. You can move, or remove it, as needed.
            this.tblKiTableAdapter.Fill(this.hocKyDataSet.tblKi);
            // TODO: This line of code loads data into the 'khoaDataSet.tblKhoa' table. You can move, or remove it, as needed.
            this.tblKhoaTableAdapter.Fill(this.khoaDataSet.tblKhoa);
            groupBox2.Hide();
            groupBox1.Show();
        }
        //Hiển thị mã giáo viên ứng với từng khoa.
        private void cbbKhoa_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //string idKhoa = cbbKhoa.SelectedValue.ToString();
                ////string sql = "select * from tblGiaoVien where IDKhoa='" + idKhoa + "'";
                ////DataTable dt = ac.GetTable(sql);
                ////cbbMaGV.DataSource = dt;
                ////cbbMaGV.DisplayMember = "IDGiaoVien";
                ////cbbMaGV.ValueMember = "IDGiaoVien";
                ////string idGiaoVien = cbbMaGV.SelectedValue.ToString();

                ////string idkhoa = cbbKhoa.SelectedValue.ToString();
                ////string sql3 = "select khoa.TenKhoa as Khoa,gv.TenGiaoVien as Ten_Giao_Vien,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and ld.IDMon=m.IDMon and khoa.IDKhoa='"+idKhoa+"' and ld.IDNhom=nh.IDNhom";
                //string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and khoa.IDKhoa='"+idKhoa+"'";
                //dataGridView1.DataSource = business.ShowData(sql3);                
            }
            catch { };
            
            //Hiển thị Bộ Môn khi chọn Khoa
            try
            {
                string IDKhoa = cbbKhoa.SelectedValue.ToString();
                string sql1 = "select * from tblBoMon where IDKhoa='" + IDKhoa + "'";
                DataTable dt = ac.GetTable(sql1);
                cbBoMon.DataSource = dt;
                cbBoMon.DisplayMember = "TenBoMon";
                cbBoMon.ValueMember = "IDBoMon";
            }
            catch { };
            
        }
        private void cbbMaGV_SelectedIndexChanged(object sender, EventArgs e)
        {
            //MessageBox.Show(cbTimKiem.SelectedItem.ToString());
            try
            {
                

                string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                //tbTenGiaoVien.Text = "";
                //string idGiaoVien=cbbMaGV.SelectedValue.ToString();            
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                tbTenGiaoVien.Text = gv;
                
            }
            catch { }
        }
        //truy vấn lịch dạy ứng với từng mã giáo viên.
        private void btLichDay_Click(object sender, EventArgs e)
        {
            try
            {
                string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                string idNamHoc = cbNamHoc.SelectedValue.ToString();
                string idKi = cbHocKi.SelectedValue.ToString();
                //string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and gv.IDGiaoVien='" + idGiaoVien + "' and ld.IDNamHoc='" + idNamHoc + "' and ld.IDKi='" + idKi + "'";
                string sql3 = "select gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and gv.IDGiaoVien='" + idGiaoVien + "' and ld.IDNamHoc='" + idNamHoc + "' and ld.IDKi='" + idKi + "'";
                dt1 = business.ShowData(sql3);
                dataGridView1.DataSource = dt1;
                //tab1_Form_XemLichDay_Load(sender, e);
                hienThiThongTinGiaoVien();
            }
            catch { MessageBox.Show("Vui lòng chọn đủ các trường phía trên","Thông báo"); }
        }

        void hienThiThongTinGiaoVien()
        {
            //Hiển thị thông tin giáo viên.
            try
            {
                //tbTenGiaoVien.Text = "";
                string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                lbThongTinGV.Text = "Giáo viên:"+gv;

                string sql5 = "select TenKhoa from tblGiaoVien gv,tblKhoa khoa where gv.IDKhoa=khoa.IDKhoa and IDGiaoVien='" + idGiaoVien + "'";
                string khoa = ac.ExcuteScalar(sql5);
                lbThongTinGV.Text += " \n Khoa: " +khoa;

                string sql3 = "select HocVi from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string hv = ac.ExcuteScalar(sql3);
                lbThongTinGV.Text += "\n Học vị:" + hv;

                string sql4 = "select bm.TenBoMon from tblGiaoVien gv,tblBoMon bm where gv.IDBoMon=bm.IDBoMon and IDGiaoVien='" + idGiaoVien + "'";
                string bm=ac.ExcuteScalar(sql4);
                lbThongTinGV.Text += " \n Bộ môn: " + bm;                
            }
            catch { lbThongTinGV.Text = "Lỗi truy cập. Vui lòng chọn mã giáo viên  " ; }               
            
        }

        private void btXuatFile_Click(object sender, EventArgs e)
        {
            //ExportToWord(sender, e);
        }
        string file = "D:\\lichDay.xls";
        private void btXuat_Click(object sender, EventArgs e)
        {            
            //XuatRaExcel.Xuat(file, "Lịch dạy của giáo viên", dt1);
            XuatRaExcel.Xuat(file, "Lịch dạy của giáo viên", dt1);
            
        }

        private void cbNamHoc_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //dt1 = ac.GetTable("stSelectLichDayTheoNam");
                //dataGridView1.DataSource = business.ShowData("stSelectLichDayTheoNam");
                string IDNamHoc = cbNamHoc.SelectedValue.ToString();
                string sql = "select * from tblKi where IDNamHoc='" + IDNamHoc + "'";
                DataTable dt = ac.GetTable(sql);
                cbHocKi.DataSource = dt;
                cbHocKi.ValueMember = "IDKi";
                cbHocKi.DisplayMember = "IDKi";

            }
            catch { }
        }

        private void cbBoMon_SelectedIndexChanged(object sender, EventArgs e)
        {
            //lấy danh sách mã giáo viên ứng với từng bộ môn.
            try
            {
                string idbm = cbBoMon.SelectedValue.ToString();
                string sql2 = "select * from tblGiaoVien where IDBoMon='" + idbm + "'";
                DataTable dt = ac.GetTable(sql2);
                cbbMaGV.DataSource = dt;
                cbbMaGV.ValueMember = "IDGiaoVien";
                cbbMaGV.DisplayMember = "IDGiaoVien";
            }
            catch { };
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void cbTimKiem_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbTimKiem.SelectedItem.ToString()=="Lịch dạy từng giáo viên")
            {
                //groupBox2.Hide();
                groupBox1.Show();
                //groupBox1.SetBounds(15, 64, 657, 153);
            }
            else
            {
                //groupBox1.Hide();
            }

            if (cbTimKiem.SelectedItem.ToString() == "Lịch dạy từng nhóm")
            {
                groupBox1.Hide();                
                groupBox2.SetBounds(15, 67, 657, 153);
                groupBox2.Show();
            }
            else
            {
                groupBox2.Hide();
            }

            if (cbTimKiem.SelectedItem.ToString() == "Lịch dạy từng khoa")
            {
                groupBox1.Hide();
                groupBox2.Hide();
                groupBox3.SetBounds(15, 67, 657, 153);
                groupBox3.Show();
            }
            else
            {
                groupBox3.Hide();
            }

            if (cbTimKiem.SelectedItem.ToString() == "Lịch dạy từng kỳ")
            {
                groupBox1.Hide();
                groupBox2.Hide();
                groupBox3.Hide();
                groupBox4.SetBounds(15, 67, 657, 153);
                groupBox4.Show();
            }
            else
            {
                groupBox4.Hide();
            }

            if (cbTimKiem.SelectedItem.ToString() == "Lịch dạy từng năm")
            {
                groupBox1.Hide();
                groupBox2.Hide();
                groupBox3.Hide();
                groupBox4.Hide();
                groupBox5.SetBounds(15, 67, 657, 153);
                groupBox5.Show();
            }
            else
            {
                groupBox5.Hide();
            }
        }

        private void cbHocKi_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void cbNamHoc1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //dt1 = ac.GetTable("stSelectLichDayTheoNam");
                //dataGridView1.DataSource = business.ShowData("stSelectLichDayTheoNam");
                string IDNamHoc = cbNamHoc.SelectedValue.ToString();
                string sql = "select * from tblKi where IDNamHoc='" + IDNamHoc + "'";
                DataTable dt = ac.GetTable(sql);
                cbHocKi1.DataSource = dt;
                cbHocKi1.ValueMember = "IDKi";
                cbHocKi1.DisplayMember = "IDKi";

            }
            catch { }
        }

        private void btLichDay1_Click(object sender, EventArgs e)
        {
            try
            {
                //string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                string idNamHoc = cbNamHoc1.SelectedValue.ToString();
                string idKi = cbHocKi1.SelectedValue.ToString();
                string idNhom = cbNhom1.SelectedValue.ToString();
                string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and ld.IDNamHoc='" + idNamHoc + "' and ld.IDKi='" + idKi + "' and ld.IDNhom='" + idNhom + "'";
                dt1 = business.ShowData(sql3);
                dataGridView1.DataSource = dt1;
            }
            catch { MessageBox.Show("Vui lòng chọn đủ các trường phía trên","Thông báo"); }
        }

        private void btXuatFile1_Click(object sender, EventArgs e)
        {
            XuatRaExcel.Xuat(file, "Lịch dạy từng nhóm", dt1);
        }

        private void cbNamHoc2_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //dt1 = ac.GetTable("stSelectLichDayTheoNam");
                //dataGridView1.DataSource = business.ShowData("stSelectLichDayTheoNam");
                string IDNamHoc = cbNamHoc.SelectedValue.ToString();
                string sql = "select * from tblKi where IDNamHoc='" + IDNamHoc + "'";
                DataTable dt = ac.GetTable(sql);
                cbHocKi2.DataSource = dt;
                cbHocKi2.ValueMember = "IDKi";
                cbHocKi2.DisplayMember = "IDKi";

            }
            catch { }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                //string idGiaoVien = cbbMaGV.SelectedValue.ToString();
                string idNamHoc = cbNamHoc2.SelectedValue.ToString();
                string idKi = cbHocKi2.SelectedValue.ToString();
                string idKhoa = cbKhoa2.SelectedValue.ToString();
                string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and ld.IDNamHoc='" + idNamHoc + "' and ld.IDKi='" + idKi + "' and khoa.IDKhoa='" + idKhoa + "'";
                dt1 = business.ShowData(sql3);
                dataGridView1.DataSource = dt1;
            }
            catch { MessageBox.Show("Vui lòng chọn đủ các trường phía trên", "Thông báo"); }
        }

        private void cbNamHoc3_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //dt1 = ac.GetTable("stSelectLichDayTheoNam");
                //dataGridView1.DataSource = business.ShowData("stSelectLichDayTheoNam");
                string IDNamHoc = cbNamHoc.SelectedValue.ToString();
                string sql = "select * from tblKi where IDNamHoc='" + IDNamHoc + "'";
                DataTable dt = ac.GetTable(sql);
                cbHocKi3.DataSource = dt;
                cbHocKi3.ValueMember = "IDKi";
                cbHocKi3.DisplayMember = "IDKi";

            }
            catch { }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                string idNamHoc = cbNamHoc3.SelectedValue.ToString();
                string idKi = cbHocKi3.SelectedValue.ToString();

                string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and ld.IDNamHoc='" + idNamHoc + "' and ld.IDKi='" + idKi + "'";
                dt1 = business.ShowData(sql3);
                dataGridView1.DataSource = dt1;
            }
            catch { MessageBox.Show("Vui lòng chọn đủ các trường phía trên", "Thông báo"); }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            XuatRaExcel.Xuat(file, "Lịch dạy của giáo viên", dt1);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            XuatRaExcel.Xuat(file, "Lịch dạy từng kỳ", dt1);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try{
            //string idGiaoVien = cbbMaGV.SelectedValue.ToString();
            string idNamHoc = cbNamHoc4.SelectedValue.ToString();
            string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,gv.TenGiaoVien as Ten_Giao_Vien,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblKhoa khoa,tblGiaoVien gv,tblNhom nh,tblMon m where ld.IDGiaoVien=gv.IDGiaoVien and gv.IDKhoa=khoa.IDKhoa and ld.IDMon=m.IDMon and ld.IDNhom=nh.IDNhom	and ld.IDNamHoc='" + idNamHoc + "'";
            dt1 = business.ShowData(sql3);
            dataGridView1.DataSource = dt1;
            }
            catch { MessageBox.Show("Vui lòng chọn đủ các trường phía trên", "Thông báo"); }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            XuatRaExcel.Xuat(file, "Lịch dạy từng năm học", dt1);
        }        
        
    }
}
